#include "StdAfx.h"
#include <string>
#include <Util/StringUtil.h>
#include "contact_op.h"
#include <interface/data_op/table_wrapper.h>

namespace aot{ namespace data{

contact_op_impl::contact_op_impl(void)
{
}

contact_op_impl::~contact_op_impl(void)
{
}

bool
contact_op_impl::insert(const icontact_info* p, int* new_id)
{
    if (!g_db_user) return false;

    std::string str_fname = p->fname->c_str();
    std::string str_sname = p->sname->c_str();
    std::string str_org = p->org->c_str();
    std::string str_org_unit = p->org_unit->c_str();
    std::string str_org_unit_title = p->org_unit_title->c_str();
    std::string str_birthday = p->birthday->c_str();
    std::string str_tel_mobile_work = p->tel_mobile_work->c_str();
    std::string str_tel_mobile_home = p->tel_mobile_home->c_str();
    std::string str_tel_fax_work = p->tel_fax_work->c_str();
    std::string str_tel_fax_work_ext = p->tel_fax_work_ext->c_str();
    std::string str_tel_fax_home = p->tel_fax_home->c_str();
    std::string str_tel_fix_work1 = p->tel_fix_work1->c_str();
    std::string str_tel_fix_work1_ext = p->tel_fix_work1_ext->c_str();
    std::string str_tel_fix_work2 = p->tel_fix_work2->c_str();
    std::string str_tel_fix_work2_ext = p->tel_fix_work2_ext->c_str();
    std::string str_tel_fix_home1 = p->tel_fix_home1->c_str();
    std::string str_tel_fix_home1_ext = p->tel_fix_home1_ext->c_str();
    std::string str_tel_fix_home2 = p->tel_fix_home2->c_str();
    std::string str_tel_fix_home2_ext = p->tel_fix_home2_ext->c_str();
    std::string str_ecx_ext = p->ecx_ext->c_str();
    std::string str_email_home = p->email_home->c_str();
    std::string str_email_work = p->email_work->c_str();
    std::string str_url_home = p->url_home->c_str();
    std::string str_url_work = p->url_work->c_str();
    std::string str_user_id = p->user_id->c_str();
    std::string str_addr_home_prov = p->addr_home_prov->c_str();
    std::string str_addr_home_city = p->addr_home_city->c_str();
    std::string str_addr_home_street = p->addr_home_street->c_str();
    std::string str_addr_work_prov = p->addr_work_prov->c_str();
    std::string str_addr_work_city = p->addr_work_city->c_str();
    std::string str_addr_work_street = p->addr_work_street->c_str();
    std::string str_remark = p->remark->c_str();
    std::string str_ring = p->ring->c_str();
    std::string str_postcode_org = p->postcode_org->c_str();
    std::string str_postcode_home = p->postcode_home->c_str();
    std::string str_qq = p->qq->c_str();
    std::string str_msn = p->msn->c_str();
    std::string str_industry = p->industry->c_str();

    remove_sin_quot(str_fname);           
    remove_sin_quot(str_sname);           
    remove_sin_quot(str_org);             
    remove_sin_quot(str_org_unit);        
    remove_sin_quot(str_org_unit_title);  
    remove_sin_quot(str_birthday);            
    remove_sin_quot(str_tel_mobile_work);   
    remove_sin_quot(str_tel_mobile_home);   
    remove_sin_quot(str_tel_fax_work);    
    remove_sin_quot(str_tel_fax_work_ext);
    remove_sin_quot(str_tel_fax_home);    
    remove_sin_quot(str_tel_fix_work1);        
    remove_sin_quot(str_tel_fix_work1_ext);
    remove_sin_quot(str_tel_fix_work2);        
    remove_sin_quot(str_tel_fix_work2_ext);  
    remove_sin_quot(str_tel_fix_home1);
    remove_sin_quot(str_tel_fix_home1_ext);  
    remove_sin_quot(str_tel_fix_home2); 
    remove_sin_quot(str_tel_fix_home2_ext);  
    remove_sin_quot(str_ecx_ext);         
    remove_sin_quot(str_email_home);      
    remove_sin_quot(str_email_work);      
    remove_sin_quot(str_url_home);        
    remove_sin_quot(str_url_work);        
    remove_sin_quot(str_user_id);            
    remove_sin_quot(str_addr_home_prov);   
    remove_sin_quot(str_addr_home_city);   
    remove_sin_quot(str_addr_home_street); 
    remove_sin_quot(str_addr_work_prov);   
    remove_sin_quot(str_addr_work_city);   
    remove_sin_quot(str_addr_work_street); 
    remove_sin_quot(str_remark);          
    remove_sin_quot(str_ring);            
    remove_sin_quot(str_postcode_org);    
    remove_sin_quot(str_postcode_home);   
    remove_sin_quot(str_qq);              
    remove_sin_quot(str_msn);             
    remove_sin_quot(str_industry);     

    std::string sql_fmt = 
        "insert into contact \
        (id,\
        group_id,\
        sex,\
        notify_birthday_sms,\
        notify_birthday_tata,\
        fname,\
        sname,\
        org,\
        org_unit,\
        org_unit_title,\
        birthday,\
        tel_mobile_work,\
        tel_mobile_home,\
        tel_fax_work,\
        tel_fax_work_ext,\
        tel_fax_home,\
        tel_fix_work1,\
        tel_fix_work1_ext,\
        tel_fix_work2,\
        tel_fix_work2_ext,\
        tel_fix_home1,\
        tel_fix_home1_ext,\
        tel_fix_home2,\
        tel_fix_home2_ext,\
        ecx_ext,\
        email_home,\
        email_work,\
        url_home,\
        url_work,\
        user_id,\
        addr_home_prov,\
        addr_home_city,\
        addr_home_street,\
        addr_work_prov,\
        addr_work_city,\
        addr_work_street,\
        remark,\
        ring,\
        postcode_org,\
        postcode_home,\
        qq,\
        msn,\
        industry) \
        values\
        (%d,%d,%d,%d,%d, \
        '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',\
        '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',\
        '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',\
        '%s','%s','%s','%s','%s','%s','%s','%s'\
        );";

    std::string sql_exe;
    int t_new_id = get_table_next_id("contact");

    sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(),
        t_new_id,
        p->group_id,
        p->sex,
        p->notify_birthday_sms,
        p->notify_birthday_tata, 

        str_fname.c_str(),           
        str_sname.c_str(),           
        str_org.c_str(),             
        str_org_unit.c_str(),        
        str_org_unit_title.c_str(),  
        str_birthday.c_str(),            
        str_tel_mobile_work.c_str(),   
        str_tel_mobile_home.c_str(),   
        str_tel_fax_work.c_str(),    
        str_tel_fax_work_ext.c_str(),
        str_tel_fax_home.c_str(),    
        str_tel_fix_work1.c_str(),        
        str_tel_fix_work1_ext.c_str(),
        str_tel_fix_work2.c_str(),        
        str_tel_fix_work2_ext.c_str(),  
        str_tel_fix_home1.c_str(),
        str_tel_fix_home1_ext.c_str(), 
        str_tel_fix_home2.c_str(), 
        str_tel_fix_home2_ext.c_str(), 
        str_ecx_ext.c_str(),         
        str_email_home.c_str(),      
        str_email_work.c_str(),      
        str_url_home.c_str(),        
        str_url_work.c_str(),        
        str_user_id.c_str(),            
        str_addr_home_prov.c_str(),   
        str_addr_home_city.c_str(),   
        str_addr_home_street.c_str(), 
        str_addr_work_prov.c_str(),   
        str_addr_work_city.c_str(),   
        str_addr_work_street.c_str(), 
        str_remark.c_str(),          
        str_ring.c_str(),            
        str_postcode_org.c_str(),    
        str_postcode_home.c_str(),   
        str_qq.c_str(),              
        str_msn.c_str(),             
        str_industry.c_str() 
        );

    *new_id = t_new_id;
    return g_db_user->exec(sql_exe.c_str());
}

bool
contact_op_impl::del(int id)
{
    std::string sql_fmt = "delete from contact where id=%d";
    std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), id);
    return g_db_user->exec(sql_exe.c_str());
}

bool
contact_op_impl::update(const icontact_info* p)
{
    if (!g_db_user) return false;

    std::string str_fname = p->fname->c_str();
    std::string str_sname = p->sname->c_str();
    std::string str_org = p->org->c_str();
    std::string str_org_unit = p->org_unit->c_str();
    std::string str_org_unit_title = p->org_unit_title->c_str();
    std::string str_birthday = p->birthday->c_str();
    std::string str_tel_mobile_work = p->tel_mobile_work->c_str();
    std::string str_tel_mobile_home = p->tel_mobile_home->c_str();
    std::string str_tel_fax_work = p->tel_fax_work->c_str();
    std::string str_tel_fax_work_ext = p->tel_fax_work_ext->c_str();
    std::string str_tel_fax_home = p->tel_fax_home->c_str();
    std::string str_tel_fix_work1 = p->tel_fix_work1->c_str();
    std::string str_tel_fix_work1_ext = p->tel_fix_work1_ext->c_str();
    std::string str_tel_fix_work2 = p->tel_fix_work2->c_str();
    std::string str_tel_fix_work2_ext = p->tel_fix_work2_ext->c_str();
    std::string str_tel_fix_home1 = p->tel_fix_home1->c_str();
    std::string str_tel_fix_home1_ext = p->tel_fix_home1_ext->c_str();
    std::string str_tel_fix_home2 = p->tel_fix_home2->c_str();
    std::string str_tel_fix_home2_ext = p->tel_fix_home2_ext->c_str();
    std::string str_ecx_ext = p->ecx_ext->c_str();
    std::string str_email_home = p->email_home->c_str();
    std::string str_email_work = p->email_work->c_str();
    std::string str_url_home = p->url_home->c_str();
    std::string str_url_work = p->url_work->c_str();
    std::string str_user_id = p->user_id->c_str();
    std::string str_addr_home_prov = p->addr_home_prov->c_str();
    std::string str_addr_home_city = p->addr_home_city->c_str();
    std::string str_addr_home_street = p->addr_home_street->c_str();
    std::string str_addr_work_prov = p->addr_work_prov->c_str();
    std::string str_addr_work_city = p->addr_work_city->c_str();
    std::string str_addr_work_street = p->addr_work_street->c_str();
    std::string str_remark = p->remark->c_str();
    std::string str_ring = p->ring->c_str();
    std::string str_postcode_org = p->postcode_org->c_str();
    std::string str_postcode_home = p->postcode_home->c_str();
    std::string str_qq = p->qq->c_str();
    std::string str_msn = p->msn->c_str();
    std::string str_industry = p->industry->c_str();

    remove_sin_quot(str_fname);           
    remove_sin_quot(str_sname);           
    remove_sin_quot(str_org);             
    remove_sin_quot(str_org_unit);        
    remove_sin_quot(str_org_unit_title);  
    remove_sin_quot(str_birthday);            
    remove_sin_quot(str_tel_mobile_work);   
    remove_sin_quot(str_tel_mobile_home);   
    remove_sin_quot(str_tel_fax_work);    
    remove_sin_quot(str_tel_fax_work_ext);
    remove_sin_quot(str_tel_fax_home);    
    remove_sin_quot(str_tel_fix_work1);        
    remove_sin_quot(str_tel_fix_work1_ext);
    remove_sin_quot(str_tel_fix_work2);        
    remove_sin_quot(str_tel_fix_work2_ext);  
    remove_sin_quot(str_tel_fix_home1);
    remove_sin_quot(str_tel_fix_home1_ext);  
    remove_sin_quot(str_tel_fix_home2); 
    remove_sin_quot(str_tel_fix_home2_ext);  
    remove_sin_quot(str_ecx_ext);         
    remove_sin_quot(str_email_home);      
    remove_sin_quot(str_email_work);      
    remove_sin_quot(str_url_home);        
    remove_sin_quot(str_url_work);        
    remove_sin_quot(str_user_id);            
    remove_sin_quot(str_addr_home_prov);   
    remove_sin_quot(str_addr_home_city);   
    remove_sin_quot(str_addr_home_street); 
    remove_sin_quot(str_addr_work_prov);   
    remove_sin_quot(str_addr_work_city);   
    remove_sin_quot(str_addr_work_street); 
    remove_sin_quot(str_remark);          
    remove_sin_quot(str_ring);            
    remove_sin_quot(str_postcode_org);    
    remove_sin_quot(str_postcode_home);   
    remove_sin_quot(str_qq);              
    remove_sin_quot(str_msn);             
    remove_sin_quot(str_industry);       

    std::string sql_fmt = 
        "update contact set \
        group_id = %d,\
        sex = %d,\
        notify_birthday_sms = %d,\
        notify_birthday_tata = %d,\
        fname='%s',\
        sname='%s',\
        org='%s',\
        org_unit='%s',\
        org_unit_title='%s',\
        birthday='%s',\
        tel_mobile_work='%s',\
        tel_mobile_home='%s',\
        tel_fax_work='%s',\
        tel_fax_work_ext='%s',\
        tel_fax_home='%s',\
        tel_fix_work1='%s',\
        tel_fix_work1_ext='%s',\
        tel_fix_work2='%s',\
        tel_fix_work2_ext='%s',\
        tel_fix_home1='%s',\
        tel_fix_home1_ext='%s',\
        tel_fix_home2='%s',\
        tel_fix_home2_ext='%s',\
        ecx_ext='%s',\
        email_home='%s',\
        email_work='%s',\
        url_home='%s',\
        url_work='%s',\
        user_id='%s',\
        addr_home_prov='%s',\
        addr_home_city='%s',\
        addr_home_street='%s',\
        addr_work_prov='%s',\
        addr_work_city='%s',\
        addr_work_street='%s',\
        remark='%s',\
        ring='%s',\
        postcode_org='%s',\
        postcode_home='%s',\
        qq='%s',\
        msn='%s',\
        industry='%s' where id=%d;";
    std::string sql_exe;
    sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(),
        p->group_id,
        p->sex,
        p->notify_birthday_sms,
        p->notify_birthday_tata, 

        str_fname.c_str(),           
        str_sname.c_str(),           
        str_org.c_str(),             
        str_org_unit.c_str(),        
        str_org_unit_title.c_str(),  
        str_birthday.c_str(),            
        str_tel_mobile_work.c_str(),   
        str_tel_mobile_home.c_str(),   
        str_tel_fax_work.c_str(),    
        str_tel_fax_work_ext.c_str(),
        str_tel_fax_home.c_str(),    
        str_tel_fix_work1.c_str(),        
        str_tel_fix_work1_ext.c_str(),
        str_tel_fix_work2.c_str(),        
        str_tel_fix_work2_ext.c_str(),  
        str_tel_fix_home1.c_str(),
        str_tel_fix_home1_ext.c_str(), 
        str_tel_fix_home2.c_str(), 
        str_tel_fix_home2_ext.c_str(), 
        str_ecx_ext.c_str(),         
        str_email_home.c_str(),      
        str_email_work.c_str(),      
        str_url_home.c_str(),        
        str_url_work.c_str(),        
        str_user_id.c_str(),            
        str_addr_home_prov.c_str(),   
        str_addr_home_city.c_str(),   
        str_addr_home_street.c_str(), 
        str_addr_work_prov.c_str(),   
        str_addr_work_city.c_str(),   
        str_addr_work_street.c_str(), 
        str_remark.c_str(),          
        str_ring.c_str(),            
        str_postcode_org.c_str(),    
        str_postcode_home.c_str(),   
        str_qq.c_str(),              
        str_msn.c_str(),             
        str_industry.c_str(),
        p->id
        );
    return g_db_user->exec(sql_exe.c_str());
}

bool
contact_op_impl::update_contact_group(int id, int group_id)
{
	std::string sql_fmt = "update contact set \
		group_id = %d  where id=%d;" ;

	std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(),
		group_id, id );

	return g_db_user->exec(sql_exe.c_str());		
}

bool
contact_op_impl::read(int id, isqlite3_table** out)
{
    std::string sql_fmt = 
        "select \
        id,\
        group_id,\
        sex,\
        notify_birthday_sms,\
        notify_birthday_tata,\
        fname,\
        sname,\
        org,\
        org_unit,\
        org_unit_title,\
        birthday,\
        tel_mobile_work,\
        tel_mobile_home,\
        tel_fax_work,\
        tel_fax_work_ext,\
        tel_fax_home,\
        tel_fix_work1,\
        tel_fix_work1_ext,\
        tel_fix_work2,\
        tel_fix_work2_ext,\
        tel_fix_home1,\
        tel_fix_home1_ext,\
        tel_fix_home2,\
        tel_fix_home2_ext,\
        ecx_ext,\
        email_home,\
        email_work,\
        url_home,\
        url_work,\
        user_id,\
        addr_home_prov,\
        addr_home_city,\
        addr_home_street,\
        addr_work_prov,\
        addr_work_city,\
        addr_work_street,\
        remark,\
        ring,\
        postcode_org,\
        postcode_home,\
        qq,\
        msn,\
        industry \
        from contact where id=%d;";
    std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), id);
    return g_db_user->exec_and_get_record(sql_exe.c_str(), out);
}

bool
contact_op_impl::read(int id, icontact_info* out)
{
    aot::data::table_helper t_table;
    bool ret =false;
    if (this->read(id, t_table))
    {
        if (t_table.get_rows_count() > 0)
        {
            t_table.set_row_index(0);

            out->id = t_table.get_field_value_int("id");

            out->group_id = t_table.get_field_value_int("group_id");
            out->sex = t_table.get_field_value_int("sex");
            out->notify_birthday_sms = t_table.get_field_value_int("notify_birthday_sms");
            out->notify_birthday_tata = t_table.get_field_value_int("notify_birthday_tata");

            out->fname->assign(t_table.get_field_value_str("fname").c_str());
            out->sname->assign(t_table.get_field_value_str("sname").c_str());
            out->org->assign(t_table.get_field_value_str("org").c_str());
            out->org_unit->assign(t_table.get_field_value_str("org_unit").c_str());
            out->org_unit_title->assign(t_table.get_field_value_str("org_unit_title").c_str());
            out->birthday->assign(t_table.get_field_value_str("birthday").c_str());
            out->tel_mobile_work->assign(t_table.get_field_value_str("tel_mobile_work").c_str());
            out->tel_mobile_home->assign(t_table.get_field_value_str("tel_mobile_home").c_str());
            out->tel_fax_work->assign(t_table.get_field_value_str("tel_fax_work").c_str());
            out->tel_fax_work_ext->assign(t_table.get_field_value_str("tel_fax_work_ext").c_str());
            out->tel_fax_home->assign(t_table.get_field_value_str("tel_fax_home").c_str());
            out->tel_fix_work1->assign(t_table.get_field_value_str("tel_fix_work1").c_str());   
            out->tel_fix_work1_ext->assign(t_table.get_field_value_str("tel_fix_work1_ext").c_str());
			out->tel_fix_work2->assign(t_table.get_field_value_str("tel_fix_work2").c_str());   
			out->tel_fix_work2_ext->assign(t_table.get_field_value_str("tel_fix_work2_ext").c_str());
			out->tel_fix_home1->assign(t_table.get_field_value_str("tel_fix_home1").c_str());   
			out->tel_fix_home1_ext->assign(t_table.get_field_value_str("tel_fix_home1_ext").c_str());
            out->tel_fix_home2->assign(t_table.get_field_value_str("tel_fix_home2").c_str());
			out->tel_fix_home2_ext->assign(t_table.get_field_value_str("tel_fix_home2_ext").c_str());
            out->ecx_ext->assign(t_table.get_field_value_str("ecx_ext").c_str());
            out->email_home->assign(t_table.get_field_value_str("email_home").c_str());
            out->email_work->assign(t_table.get_field_value_str("email_work").c_str());
            out->url_home->assign(t_table.get_field_value_str("url_home").c_str());
            out->url_work->assign(t_table.get_field_value_str("url_work").c_str());
            out->user_id->assign(t_table.get_field_value_str("user_id").c_str());
            out->addr_home_prov->assign(t_table.get_field_value_str("addr_home_prov").c_str());
            out->addr_home_city->assign(t_table.get_field_value_str("addr_home_city").c_str());
            out->addr_home_street->assign(t_table.get_field_value_str("addr_home_street").c_str());
            out->addr_work_prov->assign(t_table.get_field_value_str("addr_work_prov").c_str());
            out->addr_work_city->assign(t_table.get_field_value_str("addr_work_city").c_str());
            out->addr_work_street->assign(t_table.get_field_value_str("addr_work_street").c_str());
            out->remark->assign(t_table.get_field_value_str("remark").c_str());
            out->ring->assign(t_table.get_field_value_str("ring").c_str());
            out->postcode_org->assign(t_table.get_field_value_str("postcode_org").c_str());
            out->postcode_home->assign(t_table.get_field_value_str("postcode_home").c_str());
            out->qq->assign(t_table.get_field_value_str("qq").c_str());
            out->msn->assign(t_table.get_field_value_str("msn").c_str());
            out->industry->assign(t_table.get_field_value_str("industry").c_str());

            ret = true;
        }
    }
    return ret;
}

bool
contact_op_impl::read(int id, icontact_info** out)
{
    aot::data::icontact_info* obj = new aot::data::contact_info_impl();
    *out = obj;
    return this->read(id, obj);
}

bool 
contact_op_impl::read_all(isqlite3_table** out)
{
    std::string sql_fmt = 
        "select \
        id,\
        group_id,\
        sex,\
        notify_birthday_sms,\
        notify_birthday_tata,\
        fname,\
        sname,\
        org,\
        org_unit,\
        org_unit_title,\
        birthday,\
        tel_mobile_work,\
        tel_mobile_home,\
        tel_fax_work,\
        tel_fax_work_ext,\
        tel_fax_home,\
        tel_fix_work1,\
        tel_fix_work1_ext,\
        tel_fix_work2,\
        tel_fix_work2_ext,\
        tel_fix_home1,\
        tel_fix_home1_ext,\
        tel_fix_home2,\
        tel_fix_home2_ext,\
        ecx_ext,\
        email_home,\
        email_work,\
        url_home,\
        url_work,\
        user_id,\
        addr_home_prov,\
        addr_home_city,\
        addr_home_street,\
        addr_work_prov,\
        addr_work_city,\
        addr_work_street,\
        remark,\
        ring,\
        postcode_org,\
        postcode_home,\
        qq,\
        msn,\
        industry \
        from contact order by id;";
    std::string sql_exe = sql_fmt;
    return g_db_user->exec_and_get_record(sql_exe.c_str(), out);
}

int
contact_op_impl::get_sex(int id)
{
	std::string sql_fmt = "select sex from contact where id=%d;";
	std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), id);
	aot::data::table_helper t_table;
	bool ret = g_db_user->exec_and_get_record(sql_exe.c_str(), t_table);
	if (ret)
	{
		t_table.set_row_index(0);
		int sex = t_table.get_field_value_int("sex");
		return sex;
	}
	return 1;
}

bool
contact_op_impl::get_records_by_group(int group_id, isqlite3_table** out)
{
    std::string sql_fmt = 
        "select \
        id,\
        group_id,\
        sex,\
        notify_birthday_sms,\
        notify_birthday_tata,\
        fname,\
        sname,\
        org,\
        org_unit,\
        org_unit_title,\
        birthday,\
        tel_mobile_work,\
        tel_mobile_home,\
        tel_fax_work,\
        tel_fax_work_ext,\
        tel_fax_home,\
        tel_fix_work1,\
        tel_fix_work1_ext,\
        tel_fix_work2,\
        tel_fix_work2_ext,\
        tel_fix_home1,\
        tel_fix_home1_ext,\
        tel_fix_home2,\
        tel_fix_home2_ext,\
        ecx_ext,\
        email_home,\
        email_work,\
        url_home,\
        url_work,\
        user_id,\
        addr_home_prov,\
        addr_home_city,\
        addr_home_street,\
        addr_work_prov,\
        addr_work_city,\
        addr_work_street,\
        remark,\
        ring,\
        postcode_org,\
        postcode_home,\
        qq,\
        msn,\
        industry \
        from contact  where group_id=%d order by id";
    std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), group_id);
    return g_db_user->exec_and_get_record(sql_exe.c_str(), out);
}

int
contact_op_impl::get_count_by_group(int group_id)
{
    int total;
    std::string sql_fmt = "select count(*) as t from contact where group_id=%d";
    std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), group_id);
    total = g_db_user->exec_scalar(sql_exe.c_str());
    return total;
}

bool
contact_op_impl::get_page_records_by_group(int group_id, int count_per_page, int page_index, isqlite3_table** out)
{
    int offset = (page_index - 1) * count_per_page;
    std::string sql_fmt = 

        "select \
        id,\
        group_id,\
        sex,\
        notify_birthday_sms,\
        notify_birthday_tata,\
        fname,\
        sname,\
        org,\
        org_unit,\
        org_unit_title,\
        birthday,\
        tel_mobile_work,\
        tel_mobile_home,\
        tel_fax_work,\
        tel_fax_work_ext,\
        tel_fax_home,\
        tel_fix_work1,\
        tel_fix_work1_ext,\
        tel_fix_work2,\
        tel_fix_work2_ext,\
        tel_fix_home1,\
        tel_fix_home1_ext,\
        tel_fix_home2,\
        tel_fix_home2_ext,\
        ecx_ext,\
        email_home,\
        email_work,\
        url_home,\
        url_work,\
        user_id,\
        addr_home_prov,\
        addr_home_city,\
        addr_home_street,\
        addr_work_prov,\
        addr_work_city,\
        addr_work_street,\
        remark,\
        ring,\
        postcode_org,\
        postcode_home,\
        qq,\
        msn,\
        industry \
        from contact where group_id=%d limit %d offset %d ;";
    std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), group_id, count_per_page, offset);
    return g_db_user->exec_and_get_record(sql_exe.c_str(), out);
}

bool
contact_op_impl::insert_group(const icontact_group_info* p, int* new_id)
{
    if (!g_db_user) return false;
   
    std::string str_name = p->name->c_str();
    remove_sin_quot(str_name);

    std::string sql_fmt = 
        "insert into contact_group \
        (id, name) \
        values\
        (%d, '%s');";
   
    std::string sql_exe;
    int t_new_id = get_table_next_id("contact_group");

    sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(),
        t_new_id,
        str_name.c_str()
        );

    *new_id = t_new_id;
    return g_db_user->exec(sql_exe.c_str());
}

bool
contact_op_impl::del_group(int group_id, bool with_contact)
{
    std::string sql_fmt;
    std::string sql_exe;
    
    if ( with_contact )
    {
        sql_fmt = "delete from contact_group where id=%d; delete from contact where group_id=%d;";
        sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), group_id, group_id);
    }
    else
    {
        sql_fmt = "delete from contact_group where id=%d";
        sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), group_id);
    }
    return g_db_user->exec(sql_exe.c_str());
}

bool
contact_op_impl::update_group( const icontact_group_info* p )
{
    if (!g_db_user) return false;

    std::string str_name = p->name->c_str();
    remove_sin_quot(str_name);

    std::string sql_fmt = 
        "update contact_group set \
        name='%s' \
        where id = %d;";
    std::string sql_exe;
    sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(),
        str_name.c_str(),
        p->id
        );

    return g_db_user->exec(sql_exe.c_str());
}

bool
contact_op_impl::read_group(int group_id, isqlite3_table** out)
{
    std::string sql_fmt = 
        "select \
        id,\
        name \
        from contact_group where id=%d";
    std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), group_id);
    return g_db_user->exec_and_get_record(sql_exe.c_str(), out);
}

bool
contact_op_impl::get_groups(isqlite3_table** out)
{
    std::string sql_fmt = 
        "select \
        id,\
        name \
        from contact_group  order by id";
    std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str());
    return g_db_user->exec_and_get_record(sql_exe.c_str(), out);
}

bool
contact_op_impl::is_group_exist(const char* group_name)
{
	int total;
	std::string sql_fmt = "select count(*) as t from contact_group where name='%s';";
	std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), group_name);
	total = g_db_user->exec_scalar(sql_exe.c_str());
	return ( total > 0 );
}

int
contact_op_impl::get_group_id_by_name(const char* group_name)
{
	int id;
	std::string sql_fmt = "select id from contact_group where name='%s';";
	std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), group_name);
	id = g_db_user->exec_scalar(sql_exe.c_str());
	return id;
}

bool
contact_op_impl::is_group_contain_contact(int group_id)
{
    int total;
    std::string sql_fmt = "select count(*) as t from contact where group_id=%d;";
    std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), group_id);
    total = g_db_user->exec_scalar(sql_exe.c_str());
    return ( total > 0 );
}

int 
contact_op_impl::get_count_by_upper_name(const char* upper_fname, const char* upper_sname, void** out)
{
    int total;
    std::string sql_fmt = "select count(*) as t from contact where upper(fname)='%s' and upper(sname)='%s';";
    std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), upper_fname, upper_sname);
    total = g_db_user->exec_scalar(sql_exe.c_str());

    if ( total > 0 )
    {
        if ( out )
        {
            sql_fmt = "select id from contact where upper(fname)='%s' and upper(sname)='%s' limit 1;";
            sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), upper_fname, upper_sname);  
            int found_id = g_db_user->exec_scalar(sql_exe.c_str());

            this->read(found_id, (icontact_info**)out); 
        }
    }
    
    return total;
}

int 
contact_op_impl::get_count_by_upper_name(const char* upper_sname, isqlite3_table** out)
{
    int total = -1;

    std::string sql_fmt = "select * from contact where upper(sname)='%s';";
    std::string  sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), upper_sname);
	if ( g_db_user->exec_and_get_record(sql_exe.c_str(), out) )
	{
		total = (*out)->get_rows_count();
	}

    return total;
}

int 
contact_op_impl::get_count_by_field_and_keyword(const char* field, const char* keyword)
{
    std::string str_keyword = keyword;
    remove_sin_quot(str_keyword);
    str_keyword = "%" + str_keyword + "%";

    int total;
    std::string sql_fmt = "select count(*) as t from contact where %s like '%s';";
    std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), field, str_keyword.c_str());
    total = g_db_user->exec_scalar(sql_exe.c_str());
    return total;
}

bool 
contact_op_impl::get_page_records_by_field_and_keyword(const char* field, const char* keyword, int count_per_page, int page_index, isqlite3_table** out)
{
    std::string str_keyword = keyword;
    remove_sin_quot(str_keyword);
    str_keyword = "%" + str_keyword + "%";

    int offset = (page_index - 1) * count_per_page;
    std::string sql_fmt = 

        "select \
        id,\
        group_id,\
        sex,\
        notify_birthday_sms,\
        notify_birthday_tata,\
        fname,\
        sname,\
        org,\
        org_unit,\
        org_unit_title,\
        birthday,\
        tel_mobile_work,\
        tel_mobile_home,\
        tel_fax_work,\
        tel_fax_work_ext,\
        tel_fax_home,\
        tel_fix_work1,\
        tel_fix_work1_ext,\
        tel_fix_work2,\
        tel_fix_work2_ext,\
        tel_fix_home1,\
        tel_fix_home1_ext,\
        tel_fix_home2,\
        tel_fix_home2_ext,\
        ecx_ext,\
        email_home,\
        email_work,\
        url_home,\
        url_work,\
        user_id,\
        addr_home_prov,\
        addr_home_city,\
        addr_home_street,\
        addr_work_prov,\
        addr_work_city,\
        addr_work_street,\
        remark,\
        ring,\
        postcode_org,\
        postcode_home,\
        qq,\
        msn,\
        industry \
        from contact where %s like '%s' limit %d offset %d ;";
    std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), field, str_keyword.c_str(), count_per_page, offset);
    return g_db_user->exec_and_get_record(sql_exe.c_str(), out);
}

bool contact_op_impl::is_phone_exist( const char* phone )
{
	std::string sql_fmt =  
		"select * from contact where tel_mobile_work ='%s' or \
		tel_mobile_home ='%s' or \
		tel_fax_work ='%s' or \
		tel_fax_work_ext ='%s' or \
		tel_fax_home ='%s' or \
		tel_fix_work1 ='%s' or \
		tel_fix_work1_ext ='%s' or \
		tel_fix_work2 ='%s' or \
		tel_fix_work2_ext ='%s' or \
		tel_fix_home1 ='%s' or \
		tel_fix_home1_ext ='%s' or \
		tel_fix_home2 ='%s' or \
		tel_fix_home2_ext ='%s' or \
		user_id = '%s';";

	std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), phone, phone, phone, phone, 
		phone, phone, phone, phone, phone, phone, phone, phone, phone ,phone);
	aot::data::table_helper t_table;
	if ( g_db_user->exec_and_get_record(sql_exe.c_str(), t_table) )
	{
		return t_table.get_rows_count();
	}
	return false;
}

bool contact_op_impl::get_records_by_phone(const char* phone, isqlite3_table** out)
{
	std::string sql_fmt =  
		"select * from contact where tel_mobile_work ='%s' or \
		tel_mobile_home ='%s' or \
		tel_fax_work ='%s' or \
		tel_fax_work_ext ='%s' or \
		tel_fax_home ='%s' or \
		tel_fix_work1 ='%s' or \
		tel_fix_work1_ext ='%s' or \
		tel_fix_work2 ='%s' or \
		tel_fix_work2_ext ='%s' or \
		tel_fix_home1 ='%s' or \
		tel_fix_home1_ext ='%s' or \
		tel_fix_home2 ='%s' or \
		tel_fix_home2_ext ='%s' or \
		user_id = '%s';";
	std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), phone, phone, phone, phone, 
		phone, phone, phone, phone, phone, phone, phone, phone, phone ,phone);

	return g_db_user->exec_and_get_record(sql_exe.c_str(), out); 
}
}} /* end namespace aot/data*/